#!/usr/bin/python
# -*- coding: utf-8 -*-
import sys, re, sqlite3
"""
This script takes a SQLITE file with two tables containing articles,
retrieves article pairs by their index, and writes them in two separate files, by index order.
The output files contain articles delimited by <article> tags.

Usage:
    ./sqlite_to_text.py SQL_FILE TABLE1 TABLE2 FILE1 FILE2
Example of usage:
    ./sqlite_to_text.py wiki.db fr en wiki.fr wiki.en

SQL_FILE: path of the SQLite database file containing the articles
TABLE1: name of the SQLite table containing the 'source' articles
TABLE2: name of the SQLite table containing the 'target' articles
FILE1: path of the file where the 'source' articles will be written
FILE2: path of the file where the 'target' articles will be written
"""

def write_article(output, index, title, text):
    header = '<article id="{}">\n'.format(index)
    header += title + '\n\n'
    header = header.encode('utf-8')
    footer = "\n</article>"
    text = text.encode('utf-8')
    output.write('{}{}{}\n'.format(header, text, footer))

def main():
    try:
        input_filename, table1, table2, filename1, filename2 = sys.argv[1:]
    except:
        sys.exit('usage: {} SQL_FILE TABLE1 TABLE2 FILE1 FILE2'.format(sys.argv[0]))

    conn = sqlite3.connect(input_filename)
    cur = conn.cursor()
    rows = cur.execute('SELECT {t1}.id, {t1}.title, {t2}.title, {t1}.content, {t2}.content FROM {t1}, {t2} WHERE {t1}.id == {t2}.id'.format(t1=table1, t2=table2))

    with open(filename1, 'w') as file1, open(filename2, 'w') as file2:
        for row in rows:
            index, title1, title2, text1, text2 = row
            write_article(file1, index, title1, text1)
            write_article(file2, index, title2, text2)

    conn.close()

if __name__ == '__main__':
    main()

